package com.resataurant.Dao;

import com.resataurant.Util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

public class SaleDataDao {

    public Map<String, Integer> getSaleData() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Integer> saleData = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "SELECT \n" +
                    "    w_name, \n" +
                    "    SUM(CAST(COALESCE(w_price, '0') AS DECIMAL(10, 2)) * COALESCE(w_num, 0)) AS total_sales\n" +
                    "FROM \n" +
                    "    s_shop_log\n" +
                    "GROUP BY \n" +
                    "    w_name";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            saleData = new HashMap<>();
            while (resultSet.next()) {
                saleData.put(resultSet.getString("w_name"), resultSet.getInt("total_sales"));
            }
            return saleData;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return null;
    }

    public Map<String, Integer> getSaleDataByDate(String start, String end) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Integer> saleData = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "SELECT \n" +
                    "    w_name, \n" +
                    "    SUM(CAST(COALESCE(w_price, '0') AS DECIMAL(10, 2)) * COALESCE(w_num, 0)) AS total_sales\n" +
                    "FROM \n" +
                    "    s_shop_log\n" +
                    "WHERE \n" +
                    "    w_data BETWEEN ? AND ?\n" +
                    "GROUP BY \n" +
                    "    w_name";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, start);
            preparedStatement.setString(2, end);
            resultSet = preparedStatement.executeQuery();
            saleData = new HashMap<>();
            while (resultSet.next()) {
                saleData.put(resultSet.getString("w_name"), resultSet.getInt("total_sales"));
            }
            return saleData;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return null;
    }

    public Map<String, Integer> getSalePopular() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Integer> saleData = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "SELECT \n" +
                    "    w_name, \n" +
                    "    SUM(w_num) AS total_sales\n" +
                    "FROM \n" +
                    "    s_shop_log\n" +
                    "GROUP BY \n" +
                    "    w_name\n" +
                    "ORDER BY \n" +
                    "    total_sales DESC\n" +
                    "LIMIT 5;";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            saleData = new HashMap<>();
            while (resultSet.next()) {
                saleData.put(resultSet.getString("w_name"), resultSet.getInt("total_sales"));
            }
            return saleData;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return null;
    }

    public Map<String, Integer> getSaleUser(String id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Map<String, Integer> saleData = null;
        try {
            connection = JDBCUtils.getConnection();
            String sql = "SELECT \n" +
                    "    w_name, \n" +
                    "    total_num\n" +
                    "FROM \n" +
                    "(\n" +
                    "    SELECT \n" +
                    "        w_name, \n" +
                    "        SUM(w_num) AS total_num\n" +
                    "    FROM \n" +
                    "        s_shop_log\n" +
                    "    WHERE \n" +
                    "        w_user_id = ? \n" +
                    "    GROUP BY \n" +
                    "        w_name\n" +
                    "    ORDER BY \n" +
                    "        total_num DESC\n" +
                    "    LIMIT 5\n" +
                    ") AS top_purchases";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, id);
            resultSet = preparedStatement.executeQuery();
            saleData = new HashMap<>();
            while (resultSet.next()) {
                saleData.put(resultSet.getString("w_name"), resultSet.getInt("total_num"));
            }
            return saleData;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(connection, preparedStatement, null);
        }
        return null;
    }

}
